using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using StillVoiceCommon;
using System.Text;
using System.IO;
using System.Data.SqlClient;
using Microsoft.ApplicationBlocks.Data;
using System.Collections.Generic;

/// <summary>
/// Summary description for Company
/// </summary>

namespace TeamTime.Classes
{

    public class Company : StillVoiceCommon.DataTableRow
    {
        #region properties
        

       

        private int? supplierID;
        public int? SupplierID
        {
            get { return supplierID; }
            //set { isDeliv = value; }
        }
        private int? enterpriseID;
        public int? EnterpriseID
        {
            get { return enterpriseID; }
            //set { isDeliv = value; }
        }

        private string usernameModifiedBy;

        public new string UserNameModifiedBy
        {
            get { return usernameModifiedBy; }
            set { usernameModifiedBy = value; }
        }
        #endregion

        public Company()
        {
            this.initClass(TN5Y.Utilities.DBConnectionString, "Company");
        }
        public new void load(string id)
        {
            base.load(id);
            System.Data.SqlClient.SqlDataReader dr = SqlHelper.ExecuteReader(TN5Y.Utilities.DBConnectionString, CommandType.Text,
                "select * from ViewCompanies where ID = @ID",
                new SqlParameter("ID", id));

            if (dr.Read())
            {
                //if (dr["SupplierID"] == DBNull.Value ) {supplierID =  null;} else {supplierID=Convert.IsDBNull(.ToInt32?(dr["SupplierID"]));}
                
                //enterpriseID = dr["EnterpriseID"] == DBNull.Value ? null :(int?)dr["EnterpriseID"]; 
                supplierID = dr["SupplierID"] == DBNull.Value ? null :(int?)dr["SupplierID"]; 
                usernameModifiedBy = dr["UserNameModifiedBy"].ToString();
            }
            dr.Close();
            dr = null;
        }

        private List<Person> GetEmployeeList(DateTime ReminderDate)
        {
            List<Person> l = new List<Person>();
            string sql = "select ID from Person e ";
            if (ReminderDate != Convert.ToDateTime("1900-01-01"))// filter on dates date
            {
                sql += " inner join ViewLatestQuestionnaireResponses qr on e.ID = qr.EmployeeID ";
                //ie where the Latest Printed declaration is more than 1 month older than the Reminder date
                sql += "where CompanyID=@CompanyID and (qr.PrintedDate is null or (qr.PrintedDate is not null and qr.PrintedDate < @ReminderDate))"; //and datediff(m, @ReminderDate, qr.PrintedDate ) < -1 ";
                //TODO: check with Carl the threshold rules for the ReminderDate
            }
            else
            {
                sql += "where CompanyID=@CompanyID ";
            }
            sql += "order by Surname, FirstNames ";
            DataSet ds = SqlHelper.ExecuteDataset(TN5Y.Utilities.DBConnectionString, CommandType.Text, sql,
                 new SqlParameter("CompanyID", Convert.ToInt32(this.KeyValue)),
                 new SqlParameter("ReminderDate", ReminderDate));
            if (ds.Tables.Count > 0)
            {
                foreach (DataRow r in ds.Tables[0].Rows)
                {
                    Person employee = new Person();
                    employee.load(Convert.ToInt32(r["ID"]));
                    l.Add(employee);
                }
            }
            return l;
        }
        public List<Person> GetEmployeeList()
        {
            return GetEmployeeList(Convert.ToDateTime("1900-01-01"));
        }
        public List<Person> GetEmployeeListNonCompleters(DateTime ReminderDate)
        {
            return GetEmployeeList(ReminderDate);
        }

        internal void addPerson(int personID)
        {
            string sql = "if not exists(select ID from CompanyPerson where PersonID = @PersonID and CompanyID = @CompanyID)" +
                        " insert into CompanyPerson (PersonID, CompanyID ) values (@PersonID, @CompanyID )";
            SqlHelper.ExecuteNonQuery(TN5Y.Utilities.DBConnectionString, CommandType.Text, sql,
                                     new SqlParameter("PersonID", personID),
                                     new SqlParameter("CompanyID", Convert.ToInt32(this.KeyValue)));
        }

        internal void removePerson(int personID)
        {
            string sql = "delete from CompanyPerson where PersonID = @PersonID and CompanyID = @CompanyID";
            SqlHelper.ExecuteNonQuery(TN5Y.Utilities.DBConnectionString, CommandType.Text, sql,
                                     new SqlParameter("PersonID", personID),
                                     new SqlParameter("CompanyID", Convert.ToInt32(this.KeyValue)));
        }

        internal DataSet getPersons()
        {
            
            string sql = "select p.* , c.CompanyName,  es.PersonID  as PersonIDShareHolder " +
            " from ViewPersons p " +
            " inner join CompanyPerson cp on p.ID = cp.PersonID " +
            " inner join Company c on cp.CompanyID = c.ID " +
            " inner join Enterprise e on c.ID = e.CompanyID " +
            " left join EnterpriseShareholder es on es.EnterpriseID = @EnterpriseID and p.ID = es.PersonID " +
            " where c.ID = @CompanyID";

            return SqlHelper.ExecuteDataset(TN5Y.Utilities.DBConnectionString, CommandType.Text, sql,
                new SqlParameter("CompanyID", Convert.ToInt32(this.KeyValue)),
                new SqlParameter("EnterpriseID", this.EnterpriseID));
        }
    }

    public class Companies
    {
        public DataSet getCompanies(bool AnyOption)
        {
            string sql = "select ID, CompanyName from ViewCompanies order by CompanyName";
            if (AnyOption) sql = "select 0 as ID,'<Any>' as CompanyName UNION " + sql;
            return sqlDB.getDataSet(sql);
        }
        
        public DataSet searchCompanies(string searchFor, bool searchInNotes, string companyType, string sortExpression, Int16 ProvinceID, bool searchInActivities) 
        {
            StringBuilder sql = new StringBuilder("SELECT top 200 c.* ");
            if (searchInActivities) sql.Append(", l.Activities ");
            sql.Append("FROM ViewCompanies c ");
            if (searchInActivities)
            {
                sql.Append(" left join ViewEnterpriseActivityList l on c.EnterpriseID = l.EnterpriseID ");
            }
            
            sql.Append("where 1=1 ");
            if (searchFor != "")
            {
                sql.Append(" and (c.CompanyName like '%'+@SearchFor+'%' or");
                sql.Append(" c.CompanyNameRegistered like '%'+@SearchFor+'%' or");
                sql.Append(" c.ContactPerson like '%'+@SearchFor+'%' or");
                sql.Append(" c.CityTown like '%'+@SearchFor+'%' or");
                sql.Append(" c.Suburb like '%'+@SearchFor+'%' or");
                sql.Append(" c.AddressPostalCode like '%'+@SearchFor+'%' or");
                sql.Append(" c.ID like '%'+@SearchFor+'%' ");
                if (searchInNotes)
                {
                    sql.Append(" or");
                    sql.Append(" c.Notes like '%'+@SearchFor+'%' ");
                }
                if (searchInActivities)
                {
                    sql.Append(" or");
                    sql.Append(" l.Activities like '%'+@SearchFor+'%' ");
                }
                sql.Append(") ");
            }
            if (companyType == "Suppliers")
            {
                sql.Append(" and c.SupplierID is not null");
            }
            if (companyType == "Enterprises")
            {
                sql.Append(" and c.EnterpriseID is not null");
            }
            if (companyType == "Others")
            {
                sql.Append(" and c.SupplierID is null and c.EnterpriseID is null");
            }
            if (ProvinceID !=0)
            {
                sql.Append(" and c.ProvinceID = @ProvinceID");
            }


            if (sortExpression != "")
            {
                sql.Append(" order by c." + sortExpression);
            }
            return SqlHelper.ExecuteDataset(TN5Y.Utilities.DBConnectionString, CommandType.Text, sql.ToString(),
                new SqlParameter("SearchFor", searchFor),
                new SqlParameter("ProvinceID", ProvinceID));
        }

        public bool duplicateCompanyExists(string CompanyName, string ID)
        {
            bool ret = false;
            string sql = "select ID from Company where CompanyName = '" + CompanyName + "' ";
            if (ID != "") { sql += " and ID <> '" + ID + "'"; }//same name different id
            SqlDataReader dr;
            dr = SqlHelper.ExecuteReader(TN5Y.Utilities.DBConnectionString, CommandType.Text, sql);
            ret = dr.Read();
            dr.Close();
            return ret;
        }

        public int getDuplicateCompanyID(string CompanyName, string ID)
        {
            int ret = 0;
            string sql = "select ID from Company where CompanyName = @CompanyName  ";
            if (ID != "") { sql += " and ID <> '" + ID + "'"; }//same name different id
            SqlDataReader dr;
            using (dr = SqlHelper.ExecuteReader(TN5Y.Utilities.DBConnectionString, CommandType.Text, sql,
                new SqlParameter("CompanyName", CompanyName)))
            {
                if (dr.Read())
                {
                    ret = Convert.ToInt32(dr[0]);
                }
                dr.Close();
            }
            return ret;
        }

        public int GetCompanyIDFromName(string CompanyName)
        {
            int ret = -1;
            string sql = "select ID from Company where CompanyName = @CompanyName";

            SqlDataReader dr;
            dr = SqlHelper.ExecuteReader(TN5Y.Utilities.DBConnectionString, CommandType.Text, sql,
                new SqlParameter("CompanyName", CompanyName));
            if (dr.Read())
            {
                ret = Convert.ToInt32(dr[0].ToString());
            }
            dr.Close();
            return ret;
        }
        
        public int GetEnterpriseIDFromName(string CompanyName)
        {
            int ret = -1;
            string sql = "select ID from ViewEnterprises where CompanyName = @CompanyName";

            SqlDataReader dr;
            dr = SqlHelper.ExecuteReader(TN5Y.Utilities.DBConnectionString, CommandType.Text, sql,
                new SqlParameter("CompanyName", CompanyName));
            if (dr.Read())
            {
                ret = Convert.ToInt32(dr[0].ToString());
            }
            dr.Close();
            return ret;
        }

        internal string GetCompanyEmail(Int32 CompanyID)
        {
            string ret = "";
            string sql = "select EmailAddress from Company where ID = @CompanyID";

            SqlDataReader dr;
            dr = SqlHelper.ExecuteReader(TN5Y.Utilities.DBConnectionString, CommandType.Text, sql,
                new SqlParameter("CompanyID", CompanyID));
            if (dr.Read())
            {
                ret = dr[0].ToString();
            }
            dr.Close();
            return ret;
        }

        internal string getCompanyNameByID(int CompanyID)
        {
            string sql = "select CompanyName from Company where ID = @CompanyID";
            return SqlHelper.ExecuteScalar(TN5Y.Utilities.DBConnectionString, CommandType.Text, sql,
                            new SqlParameter("CompanyID", CompanyID)).ToString();
        }
    }

    public class Owners
    {
        public DataSet getOwners(bool AnyOption)
        {
            return this.getOwners("", AnyOption);
        }
        public DataSet getOwners(string searchFor, bool AnyOption)
        {
            string sql = "select c.ID, c.CompanyName from Owner o inner join Company c on o.CompanyID = c.ID ";
            if (searchFor != "") { sql += " where c.CompanyName like '%" + searchFor.Trim() + "%' "; }
            if (searchFor != "") { sql += "order by CompanyName"; }
            if (AnyOption) sql = "select 0 as ID,'<Any>' as CompanyName UNION " + sql;
            return sqlDB.getDataSet(sql);
        }

        public Int32 getCompanyIDByName(string companyName)
        {
            Int32 ret = 0;
            string sql = "select ID from Company where CompanyName = '" + companyName + "' ";
            SqlDataReader dr;
            dr = SqlHelper.ExecuteReader(TN5Y.Utilities.DBConnectionString, CommandType.Text, sql);
            if (dr.Read())
            {
                ret = Convert.ToInt32(dr[0]);
            }
            dr.Close();
            return ret;
        }
        

        internal string getCompanyNameByID(int companyID)
        {
            string ret = "";
            string sql = "select CompanyName from Company where ID = " + companyID.ToString();
            SqlDataReader dr;
            dr = SqlHelper.ExecuteReader(TN5Y.Utilities.DBConnectionString, CommandType.Text, sql);
            if (dr.Read())
            {
                ret = dr[0].ToString();
            }
            dr.Close();
            return ret;
        }
    }

    public class MaintenanceCompanies
    {
        public DataSet getMaintenanceCompanies(bool AnyOption)
        {
            string sql = "select c.ID, c.CompanyName from MaintenanceCompany m inner join Company c on m.CompanyID = c.ID order by c.CompanyName";
            if (AnyOption) sql = "select 0 as ID,'<Any>' as CompanyName UNION " + sql;
            return sqlDB.getDataSet(sql);
        }

    }

    public class DeliveryCompanies
    {
        public DataSet getDeliveryCompanies(bool EmptyOption)
        {
            string sql = "select ID, CompanyName from ViewDeliveryCompanies order by CompanyName";
            if (EmptyOption) sql = "select 0 as ID,'' as CompanyName UNION " + sql;
            return sqlDB.getDataSet(sql);
        }


    }

}
